﻿using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using NHibernate;
using VitCode.Castle.DataAccess;
using VitCode.Castle.Helper;
using VitCode.Web.UI;

namespace VitCode.BaseDataPageCreator
{
	/// <summary>
	/// EquipmentDataSourceBuilder 的摘要說明。
	/// </summary>
	public class UserDataSourceBuilder : PaginationGridDataSourceBuilder
	{
	    private string m_ListSql, m_KeyColName,m_SortColName;

        public UserDataSourceBuilder(string strListSql,PaginationGrid pgList)
        {
            m_ListSql = strListSql;
            m_KeyColName = pgList.DataKeyField;
            SortExpression = pgList.SortExpression;
            SetShowDataFieldSql(pgList);
        }

		private int m_RecordCount;
		private int m_PageIndex;
		private int m_PageSize;
		private StringCollection m_PrimaryKeys;
		private string m_SqlForGetAllData;
		private string m_SqlForGetIdCollection;
		private string m_SortExpression;
		private string m_ShowDataFieldSql = "";

		public void SetShowDataFieldSql(PaginationGrid pgList)
		{
			m_ShowDataFieldSql = pgList.DataKeyField;
			for (int i = 0; i < pgList.Columns.Count; i++)
			{
				IPaginationGridColumn col = pgList.Columns[i];
				if (col is PaginationGridBoundColumn || col is PaginationGridLinkColumn)
				{
					if (m_ShowDataFieldSql.Length == 0 && i == 0)
						m_ShowDataFieldSql += col.ColumnDataField;
					else
						m_ShowDataFieldSql += "," + col.ColumnDataField;
				}
			}
            
		    m_SqlForGetAllData = "Select "+m_ShowDataFieldSql+" from ("+m_ListSql+") kk";
		}

		public string SqlForGetAllData
		{
			get { return m_SqlForGetAllData; }
			set { m_SqlForGetAllData = value; }
		}

		public string SqlForGetIdCollection
		{
			get { return m_SqlForGetIdCollection; }
			set { m_SqlForGetIdCollection = value; }
		}

	    private const string SortKeyInfoFG = "_|_";
	    private const int SORT_START_INDEX = 1000000000;

        private DataTable GetSortDataTable(DataTable fixDt)
        {
            var scSortVals = new ArrayList();
            for (var i = SORT_START_INDEX; i < fixDt.Rows.Count + SORT_START_INDEX; i++)
            {
                var dr = fixDt.Rows[i-SORT_START_INDEX];
                var sortColVal = Convert.ToString(dr[m_SortColName.Trim()]);
                scSortVals.Add(sortColVal + SortKeyInfoFG + i);
            }
            scSortVals.Sort();

            var sortDt = fixDt.Clone();

            var itemAmount = scSortVals.Count;
            var isDesc = (m_SortExpression.Length > 0 && m_SortExpression.ToLower().TrimEnd().EndsWith("desc"));
            for (var i = 0; i < itemAmount; i++)
            {
                var itemIndex = i;
                if (isDesc)
                    itemIndex = (itemAmount - 1 - i);
                var str = scSortVals[itemIndex].ToString();
                var keyIndex = str.Substring(str.IndexOf(SortKeyInfoFG) + SortKeyInfoFG.Length);
                var index = Convert.ToInt32(keyIndex) - SORT_START_INDEX;
                sortDt.Rows.Add(fixDt.Rows[index].ItemArray);
            }

            return sortDt;
        }

	    private StringCollection GetPrimaryKeys()
		{
			ISession session = (new BaseDataAccess()).Session;
			StringCollection Keys = new StringCollection();
            string sql = "";
            //使用應用程式進行排序,以解決排序欄位在Sql上不一定有最高效能的問題,如果依賴DB進行排序,DB伺服器負擔會很重

            if (!string.IsNullOrEmpty(m_SortColName))
            {
                sql = "Select " + m_KeyColName + ", " + m_SortColName + " From (" + m_ListSql + ") kk ";
            }
            else
                sql = "Select " + m_KeyColName + " From (" + m_ListSql + ") kk";

			DataTable dt = new DataTable();
			try
			{
				var comm = (SqlCommand) session.Connection.CreateCommand();
				comm.CommandTimeout = 900;
				comm.CommandText = sql;
				SqlDataAdapter da = new SqlDataAdapter(comm);
				da.Fill(dt);
			}
			finally
			{
				session.Close();
			}
		    if (dt.Rows.Count > 0)
		    {
		        var sortDt = GetSortDataTable(dt);
                for (var i = 0; i < sortDt.Rows.Count; i++)
                {
                    var dr = sortDt.Rows[i];
                    var keyVale = Convert.ToString(dr[m_KeyColName.Trim()]);
                    Keys.Add(keyVale);
                }
		    }
			return Keys;
		}

		public override object BuildDataSource()
		{
			PrimaryKeys = GetPrimaryKeys();
			m_RecordCount = PrimaryKeys.Count;
			int pageCount = m_RecordCount/PageSize + 1;
		    StringBuilder sb = new StringBuilder();
			if (PageIndex < (pageCount - 1))
			{
				for (int i = PageIndex*PageSize; i < (PageIndex + 1)*PageSize; i++)
				{
                    if (sb.Length > 0)
                        sb.Append(" or ");
					sb.Append(m_KeyColName  + "='" + PrimaryKeys[i] + "'");
				}
			}
			else
			{
				for (int i = PageIndex*PageSize; i < m_RecordCount; i++)
				{
                    if (sb.Length > 0)
                        sb.Append(" or ");
                    sb.Append(m_KeyColName + "='" + PrimaryKeys[i] + "'");
				}
			}
			SqlHelper helper = new SqlHelper();
			ISession session = (new BaseDataAccess()).Session;
			DataTable dt = null;
            if (sb.Length == 0)
                sb.Append("1=2");
		    string sql = "Select * from (" + m_ListSql + ") kk Where ("+sb+") ";
            if (m_SortColName.Length > 0)
                sql += " order by " + m_SortColName;
			try
			{
				dt = helper.ExecuteDataSet(session, sql).Tables[0];
			}
			finally
			{
				session.Close();
			}
			return dt;
		}


		public StringCollection PrimaryKeys
		{
			get { return m_PrimaryKeys; }
			set { m_PrimaryKeys = value; }
		}

		public override object GetAllData(ArrayList fieldTable)
		{
			ISession session = (new BaseDataAccess()).Session;
			string sql = SqlForGetAllData;
			DataTable dt = new DataTable();
			try
			{
				SqlCommand comm = (SqlCommand) session.Connection.CreateCommand();
				SqlDataAdapter da = new SqlDataAdapter(comm);
				da.SelectCommand.CommandTimeout = 900;
				da.SelectCommand.CommandText = sql;
				da.Fill(dt);
			}
			finally
			{
				session.Close();
			}

            if (dt.Rows.Count > 0)
            {
                return GetSortDataTable(dt);
            }
		    return dt;
		}

		/// <summary>
		/// 記錄總數
		/// </summary>
		public override int RecordCount
		{
			get { return m_RecordCount; }
		}

		/// <summary>
		/// 當前頁索引
		/// </summary>
		public override int PageIndex
		{
			get { return m_PageIndex; }
			set { m_PageIndex = value; }
		}

		/// <summary>
		/// 頁面記錄條數
		/// </summary>
		public override int PageSize
		{
			get { return m_PageSize; }
			set { m_PageSize = value; }
		}

		/// <summary>
		/// 排序運算式
		/// </summary>
		public override sealed string SortExpression
		{
			get { return m_SortExpression; }
			set
			{
                m_SortExpression = value;
                if (m_SortExpression.IndexOf(" ") > 0)
                    m_SortColName = m_SortExpression.Substring(0, m_SortExpression.IndexOf(" ")) + " ";
                else
                    m_SortColName = m_SortExpression;
			}
		}
	}
}
